<!DOCTYPE html>
<html lang="en">
<head>
    <title>囧么肥事</title>
    <meta charset="UTF-8"/>
    <meta name="viewport"
          content="width=device-width,height=device-height,initial-scale=1.0,maximum-scale=1.0,user-scalable=0"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta http-equiv="Content-Type" content="text/html;"/>
    <meta name="renderer" content="webkit">
    <!--解决页面加载网络图片的403问题 , 可以让img标签预加载网络图片-->
    <meta name="referrer" content="no-referrer" />

    <!--markdown S -->
    <link rel="stylesheet" type="text/css" href="../../static/css/export_setting_css.css"/>
    <link rel="stylesheet" type="text/css" href="../../static/css/markdown_preview_css.css"/>
    <!--markdown E -->

    <!--left bar tools css E-->
    <link rel="stylesheet" type="text/css" href="../../static/css/nav.css">
    <link rel="stylesheet" type="text/css" href="http://www.jq22.com/jquery/font-awesome.4.6.0.css">
    <!--left bar tools css E-->

    <!-- 滚动条 S -->
    <link rel="stylesheet" href="../../static/css/optiscroll.css">
    <!-- 滚动条 E -->
</head>
<body>
<div id="m-wrapper" class="m-wrapper optiscroll">
    <!-- top 锚点 -->
    <a id="top"></a>

    <!-- left bar tools S -->
    <div class="leftNav-item">
        <ul>
            <li>
                <i class="fa fa-location-arrow"></i>
                <a href="http://jiongmefeishi.gitee.io/monster-blog/" target="_blank" class="rota">个人网站</a>
            </li>

            <li title="Git">
                <i class="fa fa-code-fork" aria-hidden="true"></i>
                <a href="http://jiongmefeishi.gitee.io/monster-blog/" target="_blank" class="rota">Git Page</a>
            </li>

            <li lay-data="img" data-fooc="http://resource.zqtaotao.cn/wx/qrcode/jiongmefeishi.jpg">
                <i class="fa fa-comments-o"></i><a href="javascript:;" class="rota">公众号</a>
            </li>

            <li lay-data="img" data-fooc="http://resource.zqtaotao.cn/weixincode.png">
                <i class="fa fa-money"></i><a href="javascript:;" class="rota">小额打赏</a>
            </li>
            <li title="返回顶部" class="for-top">
                <i class="fa fa-arrow-up"></i>
                <a href="#top" class="rota">去顶部</a>
            </li>
        </ul>
    </div>
    <!-- left bar tools E -->

    <!--content S-->
    <div id="export_content"><div class="output_wrapper" id="output_wrapper_id"><figure><img src="https://img-blog.csdnimg.cn/img_convert/f624544138ac5e8f9d6851002393486d.png" alt="" title=""><figcaption></figcaption></figure>
        <p>我是肥哥，一名不专业的面试官！</p>
        <p>我是囧囧，一名积极找工作的小菜鸟！</p>
        <blockquote>
            <p><strong>囧囧表示：小白面试最怕的就是面试官问的知识点太笼统，自己无法快速定位到关键问题点！！！</strong></p>
        </blockquote>
        <hr>
        <p>本期主要面试考点</p>
        <pre><code class="hljs">面试官考点之什么情况下会索引失效？<br>面试官考点之简单说说你工作中遇到的索引失效场景？<br></code></pre>
        <p>以下索引失效的常见场景</p>
        <blockquote>
            <p>1、like通配符，左侧开放情况下，全表扫描<br>2、or条件筛选，可能会导致索引失效<br>3、where中对索引列使用mysql的内置函数，一定失效<br>4、where中对索引列进行运算（如，+、-、*、/），一定失效<br>5、类型不一致，隐式的类型转换，导致的索引失效<br>6、where语句中索引列使用了负向查询，可能会导致索引失效。负向查询包括：NOT、!=、&lt;&gt;、!&lt;、!&gt;、NOT IN、NOT LIKE等，其中：!&lt; !&gt; SQLServer语法。<br>7、索引字段可以为null，使用is null或is not null时，可能会导致索引失效<br>8、隐式字符编码转换导致的索引失效<br>9、联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效<br>10、MySQL优化器的最终选择，不走索引</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/fdaee4537f455cdee728cafbc6c9d3b0.png" alt="索引失效验证1" title="索引失效验证1"><figcaption>索引失效验证1</figcaption></figure>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/dab3e4dd86a42e6499fa054873f685e5.png" alt="索引失效验证1" title="索引失效验证1"><figcaption>索引失效验证1</figcaption></figure>
        <h3 id="h"><span>验证准备</span></h3>
        <p>准备数据表，同时建立普通索引 idx_user_name</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span>&nbsp;<span class="hljs-keyword">TABLE</span>&nbsp;<span class="hljs-string">`t_user`</span>&nbsp;(<br>&nbsp;&nbsp;<span class="hljs-string">`id`</span>&nbsp;<span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>)&nbsp;<span class="hljs-keyword">NOT</span>&nbsp;<span class="hljs-literal">NULL</span>,<br>&nbsp;&nbsp;<span class="hljs-string">`user_name`</span>&nbsp;<span class="hljs-built_in">varchar</span>(<span class="hljs-number">32</span>)&nbsp;<span class="hljs-built_in">CHARACTER</span>&nbsp;<span class="hljs-keyword">DEFAULT</span>&nbsp;<span class="hljs-literal">NULL</span>&nbsp;<span class="hljs-keyword">COMMENT</span>&nbsp;<span class="hljs-string">'用户名'</span>,<br>&nbsp;&nbsp;<span class="hljs-string">`address`</span>&nbsp;<span class="hljs-built_in">varchar</span>(<span class="hljs-number">255</span>)&nbsp;<span class="hljs-built_in">CHARACTER</span>&nbsp;<span class="hljs-keyword">DEFAULT</span>&nbsp;<span class="hljs-literal">NULL</span>&nbsp;<span class="hljs-keyword">COMMENT</span>&nbsp;<span class="hljs-string">'地址'</span>,<br>&nbsp;&nbsp;<span class="hljs-string">`create_time`</span>&nbsp;datetime&nbsp;<span class="hljs-keyword">DEFAULT</span>&nbsp;<span class="hljs-literal">NULL</span>&nbsp;<span class="hljs-keyword">ON</span>&nbsp;<span class="hljs-keyword">UPDATE</span>&nbsp;<span class="hljs-keyword">CURRENT_TIMESTAMP</span>&nbsp;<span class="hljs-keyword">COMMENT</span>&nbsp;<span class="hljs-string">'创建时间'</span>,<br>&nbsp;&nbsp;PRIMARY&nbsp;<span class="hljs-keyword">KEY</span>&nbsp;(<span class="hljs-string">`id`</span>)<br>)&nbsp;<span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span>&nbsp;<span class="hljs-keyword">DEFAULT</span>&nbsp;<span class="hljs-keyword">CHARSET</span>=utf8;<br></code></pre>
        <p>插入1万条数据（注意：数据多些，mysql不走索引情况之一是<strong>数据量非常少</strong>，MySQL<strong>查询优化器</strong>认为全表扫描比使用索引更快，导致索引失效，explain检查是否使用索引时，发现无法走索引）</p>
        <pre><code class="hljs delphi">--&nbsp;创建存储过程，插入<span class="hljs-number">10000</span>用户信息<br>CREATE&nbsp;<span class="hljs-function"><span class="hljs-keyword">PROCEDURE</span>&nbsp;<span class="hljs-title">user_insert</span><span class="hljs-params">()</span><br>--&nbsp;定义存储过程开始<br><span class="hljs-title">BEGIN</span><br>&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;定义变量&nbsp;<span class="hljs-title">i</span>&nbsp;，<span class="hljs-title">int</span>&nbsp;类型，默认值为&nbsp;1<br>&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-title">DECLARE</span>&nbsp;<span class="hljs-title">i</span>&nbsp;<span class="hljs-title">INT</span>&nbsp;<span class="hljs-title">DEFAULT</span>&nbsp;1;</span><br><br>&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">WHILE</span>&nbsp;i&nbsp;&lt;=&nbsp;<span class="hljs-number">10000</span><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--&nbsp;定义循环内执行命令<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">DO</span>&nbsp;INSERT&nbsp;INTO&nbsp;t_user(id,&nbsp;user_name,&nbsp;address,&nbsp;create_time)&nbsp;VALUES(i,&nbsp;CONCAT(<span class="hljs-string">'mayun'</span>,&nbsp;i),&nbsp;CONCAT(<span class="hljs-string">'浙江杭州'</span>,&nbsp;i),&nbsp;now());<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">SET</span>&nbsp;i=i+<span class="hljs-number">1</span>;<br>&nbsp;&nbsp;&nbsp;&nbsp;<span class="hljs-keyword">END</span>&nbsp;<span class="hljs-keyword">WHILE</span>;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;COMMIT;<br><span class="hljs-keyword">END</span>;<br>--&nbsp;定义存储过程结束<br><br><br>--&nbsp;调用存储工程<br>CALL&nbsp;user_insert();<br></code></pre>
        <h3 id="hor"><span>一、OR索引失效验证</span></h3>
        <p>好多人说where条件中使用 or ，那么索引一定失效，是否正确？</p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/ebd5987cae7ca16b874292c659249771.png" alt="or查询" title="or查询"><figcaption>or查询</figcaption></figure>
        <blockquote>
            <p>OR 连接的是同一个字段，相同走索引</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'mayun10'</span>&nbsp;<span class="hljs-keyword">or</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'mayun1000'</span><br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/ebe300829092cdf115cee9b5ccb88d9c.png" alt="or查询走索引情况" title="or查询走索引情况"><figcaption>or查询走索引情况</figcaption></figure>
        <blockquote>
            <p>OR 连接的是两个不同字段，不同索引失效</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'mayun10'</span>&nbsp;<span class="hljs-keyword">or</span>&nbsp;address&nbsp;=&nbsp;<span class="hljs-string">'浙江杭州12'</span><br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/d79413e9c57d7ccdc218274574e619bc.png" alt="or查询索引失效情况" title="or查询索引失效情况"><figcaption>or查询索引失效情况</figcaption></figure>
        <blockquote>
            <p>给address列增加索引</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">alter</span>&nbsp;<span class="hljs-keyword">table</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">add</span>&nbsp;<span class="hljs-keyword">index</span>&nbsp;idx_address&nbsp;(address)<br></code></pre>
        <blockquote>
            <p>OR 连接的是两个不同字段，如果两个字段皆有索引，走索引</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/cf33f854ad45d4de6d1e351c5f6c5c1b.png" alt="or查询走索引情况-两边字段有索引" title="or查询走索引情况-两边字段有索引"><figcaption>or查询走索引情况-两边字段有索引</figcaption></figure>
        <p><strong>验证总结</strong></p>
        <p>or 可能会导致索引失效，并非一定，这里涉及到MySQL <strong>index merge</strong> 技术。</p>
        <blockquote>
            <p>1、MySQL5.0之前，查询时一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。</p>
            <p>2、但是从5.1开始，MySQL引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。然后将它们各自的结果进行合并(intersect/union)。</p>
        </blockquote>
        <p><strong>or索引生效有哪些情况？</strong></p>
        <p>第一种 or两边连接的是<strong>同一个索引字段</strong></p>
        <p>第二种 or两边连接的是两个索引字段，即<strong>两个字段分别都建立了索引</strong></p>
        <h3 id="hlike"><span>二、LIKE通配符索引失效验证</span></h3>
        <p>一个最常见的查询场景，建立idx_user_name索引</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-string">'%mayun100%'</span>;<br></code></pre>
        <blockquote>
            <p>这条查询是否走索引？</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/9e8c11d5c736e09dbdce4ffca3db0a1a.png" alt="like不走索引" title="like不走索引"><figcaption>like不走索引</figcaption></figure>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-string">'mayun100%'</span>;<br></code></pre>
        <blockquote>
            <p>这条查询是否走索引？</p>
        </blockquote>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/299ca6fc7324e10e593ca32135b3402b.png" alt="like走索引" title="like走索引"><figcaption>like走索引</figcaption></figure>
        <p><strong>验证总结</strong></p>
        <pre><code class="hljs nginx"><span class="hljs-attribute">like</span>&nbsp;通配符特性是可以左右开闭匹配查询<br><br>当左边开放使用&nbsp;%&nbsp;或者&nbsp;_&nbsp;匹配的时候都不会走索引,会进行全表扫描<br></code></pre>
        <blockquote>
            <p>为什么左开情况下会索引失效?请介绍一下原理！</p>
        </blockquote>
        <p>我们知道建立索引后，MySQL会建立一棵有序的B+Tree,索引树是有序的，索引列进行查询匹配时是从左到右进行匹配。使用 % 和 _ 匹配，这表示左边匹配值是不确定的。不确定，意味着充满可能，<strong>怎么比较？</strong></p>
        <p>当然只能一个一个的比较，那就相当于，全匹配了，全匹配在优化器看来，与其走索引树查询，再进行不断的回表操作，还不如直接进行全表扫描划算！</p>
        <h3 id="hwheremysql"><span>三、where中对索引列使用mysql的内置函数</span></h3>
        <blockquote>
            <p>建立 idx_age 索引，</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">alter</span>&nbsp;<span class="hljs-keyword">table</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">add</span>&nbsp;<span class="hljs-keyword">index</span>&nbsp;idx_age(age);<br></code></pre>
        <blockquote>
            <p>不使用内置函数</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;age&nbsp;=&nbsp;<span class="hljs-number">80</span><br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/2ea0a35d5a1cddc38c950eb440d9c392.png" alt="内置函数验证-不使用内置函数" title="内置函数验证-不使用内置函数"><figcaption>内置函数验证-不使用内置函数</figcaption></figure>
        <blockquote>
            <p>使用内置函数</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;<span class="hljs-keyword">abs</span>(age)&nbsp;=&nbsp;<span class="hljs-number">80</span><br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/6e762ab24d2f654b608f28dec0a01678.png" alt="内置函数验证-内置函数" title="内置函数验证-内置函数"><figcaption>内置函数验证-内置函数</figcaption></figure>
        <p><strong>验证总结</strong></p>
        <blockquote>
            <p>如果对索引字段做了函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。</p>
            <p>MySQL 无法再使用索引快速定位功能，而只能使用全索引扫描。</p>
        </blockquote>
        <h3 id="hwhere"><span>四、where中对索引列进行运算（如，+、-、*、/），一定失效</span></h3>
        <blockquote>
            <p>不涉及索引列的运算</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">alter</span>&nbsp;<span class="hljs-keyword">table</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">add</span>&nbsp;<span class="hljs-keyword">index</span>&nbsp;idx_age(age);<br><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;age&nbsp;=&nbsp;<span class="hljs-number">80</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/2ea0a35d5a1cddc38c950eb440d9c392.png" alt="内置函数验证-不使用内置函数" title="内置函数验证-不使用内置函数"><figcaption>内置函数验证-不使用内置函数</figcaption></figure>
        <blockquote>
            <p>索引列进行运算操作</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;age&nbsp;+&nbsp;<span class="hljs-number">5</span>&nbsp;=&nbsp;<span class="hljs-number">80</span><br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/ba72960b6f419edba01ba8034aed74de.png" alt="索引运算失效" title="索引运算失效"><figcaption>索引运算失效</figcaption></figure>
        <h3 id="h-1"><span>五、类型不一致，隐式的类型转换，导致的索引失效</span></h3>
        <pre><code class="hljs sql"><span class="hljs-keyword">alter</span>&nbsp;<span class="hljs-keyword">table</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">add</span>&nbsp;<span class="hljs-keyword">index</span>&nbsp;idx_user_name(user_name);<br><br><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'mayun1'</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/d95f4dbd6f8fc8530bd014dc4053dc49.png" alt="类型不一致验证之正常类型" title="类型不一致验证之正常类型"><figcaption>类型不一致验证之正常类型</figcaption></figure>
        <p>修改数据，再次explain</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">update</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">set</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'100'</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-string">'mayun1'</span>;<br><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;=&nbsp;<span class="hljs-number">100</span>;<br></code></pre>
        <p>user_name = 100 ，因为user_name 字段定义的是varchar，索引在where进行匹配时会先隐式调用 case() 函数进行类型转换 将匹配条件变成，<strong>user_name = '100'</strong></p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/c9a148971d95dd177388b1ee947b51ad.png" alt="类型不一致验证之类型转换" title="类型不一致验证之类型转换"><figcaption>类型不一致验证之类型转换</figcaption></figure>
        <h3 id="hwhere-1"><span>六、where语句中索引列使用了负向查询，可能会导致索引失效。</span></h3>
        <blockquote>
            <p>负向查询包括：NOT、!=、&lt;&gt;、!&lt;、!&gt;、NOT IN、NOT LIKE等，其中：!&lt; !&gt; SQLServer语法。</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">alter</span>&nbsp;<span class="hljs-keyword">table</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">add</span>&nbsp;<span class="hljs-keyword">index</span>&nbsp;idx_age(age);<br><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;age&nbsp;<span class="hljs-keyword">in</span>&nbsp;(<span class="hljs-number">100</span>,&nbsp;<span class="hljs-number">50</span>);<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/08ddf1876ddb1cb73b407a0e5848ac7a.png" alt="反向选择之in" title="反向选择之in"><figcaption>反向选择之in</figcaption></figure>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;age&nbsp;<span class="hljs-keyword">not</span>&nbsp;<span class="hljs-keyword">in</span>&nbsp;(<span class="hljs-number">100</span>,&nbsp;<span class="hljs-number">50</span>);<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/f7f7c224b7c7338b5e4643706c4a085a.png" alt="反向选择之not_in" title="反向选择之not_in"><figcaption>反向选择之not_in</figcaption></figure>
        <h3 id="hnullisnullisnotnull"><span>七、索引字段可以为null，使用is null或is not null时，可能会导致索引失效</span></h3>
        <p><strong>第一种情况，表结构规定允许user_name 字段可以为null</strong></p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/1190666537145bcc317bcb2794505dbb.png" alt="null可以为空" title="null可以为空"><figcaption>null可以为空</figcaption></figure>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">is</span>&nbsp;<span class="hljs-literal">null</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/c2d205da5711773bc0c8d7520dc9edeb.png" alt="null可以为空校验之is_null" title="null可以为空校验之is_null"><figcaption>null可以为空校验之is_null</figcaption></figure>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">is</span>&nbsp;<span class="hljs-keyword">not</span>&nbsp;<span class="hljs-literal">null</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/4c04744d04e91f4d9f0395041d51ca09.png" alt="null可以为空校验之is_not_null" title="null可以为空校验之is_not_null"><figcaption>null可以为空校验之is_not_null</figcaption></figure>
        <p><strong>第二种情况，表结构规定user_name 字段不可以为null</strong></p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/0eac42d920909363b181b77a563736f8.png" alt="null不允许为null" title="null不允许为null"><figcaption>null不允许为null</figcaption></figure>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">is</span>&nbsp;<span class="hljs-literal">null</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/608cddb196a3262e6a77f62412e49625.png" alt="null不允许为null之null" title="null不允许为null之null"><figcaption>null不允许为null之null</figcaption></figure>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">is</span>&nbsp;<span class="hljs-keyword">not</span>&nbsp;<span class="hljs-literal">null</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/fdf32f1593562196bc72d77e89eaab30.png" alt="null不允许为null之is_not_null" title="null不允许为null之is_not_null"><figcaption>null不允许为null之is_not_null</figcaption></figure>
        <h3 id="h-2"><span>八、隐式字符编码转换导致的索引失效</span></h3>
        <p>当两个表进行连接JOIN 时，如果两张表的字符编码不同，可能会导致索引失效。</p>
        <p>这个索引失效场景尚未遇到，网上很多文章说会导致索引失效，查阅发现大量的博客说UTF8mb4字符集的表mb4与UTF8字符集的表utf8 关联会产生索引失效的问题，但是我根据大量博文所述操作，发现暂时还是无法复现，读者可自行查阅。</p>
        <blockquote>
            <p>如果读者复现到此场景，欢迎评论讨论或关注如果读者复现到此场景，欢迎评论或关注公众号<code>囧么肥事</code>讨论</p>
        </blockquote>
        <h3 id="hwhere-2"><span>九、联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效</span></h3>
        <blockquote>
            <p>创建联合做引 idx_user_name_deposit， <strong>遵循最左匹配原则</strong></p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">alter</span>&nbsp;<span class="hljs-keyword">table</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">add</span>&nbsp;<span class="hljs-keyword">index</span>&nbsp;idx_user_name_deposit(user_name,&nbsp;deposit);<br><br><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-string">'mayun86%'</span><br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/f87dd3a7ebb7b0c47c6fca20583f9c3c.png" alt="最左匹配之a" title="最左匹配之a"><figcaption>最左匹配之a</figcaption></figure>
        <blockquote>
            <p><strong>遵循最左匹配之 a b 类型</strong></p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-string">'mayun86%'</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;deposit&nbsp;=&nbsp;<span class="hljs-number">5620.26</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/f978c19249a3965ae6631daa59619fca.png" alt="最左匹配之ab" title="最左匹配之ab"><figcaption>最左匹配之ab</figcaption></figure>
        <blockquote>
            <p><strong>调换索引位置，测试联合索引书写规则</strong></p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;deposit&nbsp;=&nbsp;<span class="hljs-number">5620.26</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;user_name&nbsp;<span class="hljs-keyword">like</span>&nbsp;<span class="hljs-string">'mayun86%'</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/e208beee12b38f82d8692a6a23fbeef7.png" alt="最左匹配之ba" title="最左匹配之ba"><figcaption>最左匹配之ba</figcaption></figure>
        <blockquote>
            <p>违反最左匹配原则</p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;deposit&nbsp;=&nbsp;<span class="hljs-number">5620.26</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/054200407d8e01a390e662a97ba96903.png" alt="最左匹配之b" title="最左匹配之b"><figcaption>最左匹配之b</figcaption></figure>
        <p><strong>验证总结</strong></p>
        <p>联合索引依据最左匹配原则建立索引树，在查询时依据联合索引顺序依次匹配索引值，查询时如果违背最左匹配原则，将导致索引失效。</p>
        <pre><code class="hljs">假设建立索引&nbsp;idx_a_b_c，相当于建立了&nbsp;(a),&nbsp;(a,b),&nbsp;(a,b,c)三个索引<br><br>查询匹配时匹配顺序是&nbsp;a&nbsp;b&nbsp;c&nbsp;<br><br>查询时如果没有&nbsp;a&nbsp;字段筛选，那么索引将失效<br></code></pre>
        <blockquote>
            <p><strong>举栗子，走索引情况</strong></p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;<span class="hljs-keyword">test</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;a=<span class="hljs-number">1</span>&nbsp;<br><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;<span class="hljs-keyword">test</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;a=<span class="hljs-number">1</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;b=<span class="hljs-number">2</span>&nbsp;<br><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;<span class="hljs-keyword">test</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;a=<span class="hljs-number">1</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;b=<span class="hljs-number">2</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;c=<span class="hljs-number">3</span><br></code></pre>
        <blockquote>
            <p><strong>索引失效呢？</strong></p>
        </blockquote>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;<span class="hljs-keyword">test</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;b=<span class="hljs-number">2</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;c=<span class="hljs-number">3</span><br></code></pre>
        <p>联合索引如果要走索引，查询条件中必须要<strong>包含第一个索引</strong>，否则索引失效</p>
        <pre><code class="hljs sql"><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;<span class="hljs-keyword">test</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;b=<span class="hljs-number">1</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;a=<span class="hljs-number">1</span><br><br><span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;<span class="hljs-keyword">test</span>&nbsp;<span class="hljs-keyword">where</span>&nbsp;m=<span class="hljs-string">'222'</span>&nbsp;<span class="hljs-keyword">and</span>&nbsp;a=<span class="hljs-number">1</span><br></code></pre>
        <p><strong>这两条查询走索引的原因是什么？</strong></p>
        <p>最左前缀指的是查询时匹配索引列要按照联合索引创建的顺序，但是在书写时不需要严格按照联合索引创建的顺序，MySQL优化器会自动调整，所以上面两条查询索引有效！</p>
        <h3 id="hmysql"><span>十、MySQL优化器的最终选择，不走索引</span></h3>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;age&nbsp;&gt;&nbsp;<span class="hljs-number">59</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/5340bb0802595a28caebe23a55ad2b09.png" alt="优化器不走" title="优化器不走"><figcaption>优化器不走</figcaption></figure>
        <pre><code class="hljs sql"><span class="hljs-keyword">explain</span>&nbsp;<span class="hljs-keyword">select</span>&nbsp;*&nbsp;<span class="hljs-keyword">from</span>&nbsp;t_user&nbsp;<span class="hljs-keyword">where</span>&nbsp;age&nbsp;&gt;&nbsp;<span class="hljs-number">99</span>;<br></code></pre>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/4e7608929f86e86e9938721c2be89a62.png" alt="优化器走" title="优化器走"><figcaption>优化器走</figcaption></figure>
        <p><strong>验证总结</strong></p>
        <p>MySQL查询索引失效的情况有很多，即使其他情况都规避，但是在经过了优化器的确定查询方案的时候，依然可能索引失效。</p>
        <p>优化器会考虑<strong>查询成本</strong>，来确认它认为的最佳方案来执行查询</p>
        <p>当数据量较少，或者需要访问行很多的时候</p>
        <p>优化器会认为<strong>走索引树来进行回表</strong>，还不如直接进行全表扫描的时候，优化器将会<strong>抛弃</strong>走索引树。</p>
        <hr>
        <p><strong>推荐MySQL相关休闲阅读</strong>：</p>
        <p>第一段，索引面试题推荐阅读一：<a href="https://mp.weixin.qq.com/s?__biz=Mzg3NjU0NDE4NQ==&amp;mid=2247483708&amp;idx=1&amp;sn=2bba08c79535caad22571efa8f698aa6&amp;chksm=cf31e8eaf84661fc013c8fffd5580f5793c157c5639afdf1a3daa2381be4f6a3347690c770b7#rd">【来自面试官一面MySQL索引的连续灵魂拷问】</a></p>
        <p>第二段，索引面试题推荐阅读二：<a href="https://mp.weixin.qq.com/s?__biz=Mzg3NjU0NDE4NQ==&amp;mid=2247483727&amp;idx=1&amp;sn=de099056011f1da943a0f42843e0c75b&amp;chksm=cf31e899f846618f8d8135ace8b3ca3987a255295b603c463eecd5321806a51cc45e5815ece3#rd">【来自面试官二面MySQL索引的连续灵魂拷问】</a></p>
        <p>第三段，索引失效场景面试题推荐阅读：<a href="https://mp.weixin.qq.com/s?__biz=Mzg3NjU0NDE4NQ==&amp;mid=2247483801&amp;idx=1&amp;sn=14f7f9e7023abf045c3322a00815f79b&amp;chksm=cf31e84ff8466159498f5db66ed3c028587caabdeabe57aaa5f4612c00ea236e6c6e5bf77a66#rd">【面试官：说说你遇到的MySQL索引失效场景吧，你是如何解决的？】</a></p>
        <p>第四段，查询缓存面试题推荐阅读：<a href="https://mp.weixin.qq.com/s?__biz=Mzg3NjU0NDE4NQ==&amp;mid=2247483836&amp;idx=1&amp;sn=24d090fc782b3855ab4c8c20d139892e&amp;chksm=cf31e86af846617cbee4c7e2deb62fc18fe6e5099e2c20f7cd029a1dcbf9fb4646cfb02a4474#rd">【面试官：什么场景下会导致MySQL缓存失效？生产环境到底要不要开启MySQL缓存？】</a></p>
        <p>第五段，待更新？推荐休闲阅读：<a href="https://mp.weixin.qq.com/mp/appmsgalbum?__biz=Mzg3NjU0NDE4NQ==&amp;action=getalbum&amp;album_id=2218140423993212933#wechat_redirect">【囧么肥事】</a></p>
        <p>更多精彩内容，欢迎关注微信公众号：<strong>囧么肥事</strong> (或搜索：jiongmefeishi)</p>
        <figure><img src="https://img-blog.csdnimg.cn/img_convert/cb3a296f8edbcc70370d4eb569c40634.png" alt="囧么肥事" title="囧么肥事"><figcaption>囧么肥事</figcaption></figure></div></div>
    <!--content E-->
</div>



</body>
<!--left bar tools js S-->
<script src="http://www.jq22.com/jquery/jquery-1.10.2.js"></script>
<script type="text/javascript" src="../../static/js/nav.js"></script>
<!--left bar tools js E-->

<!-- 滚动条 S -->
<script type="text/javascript" src="../../static/js/optiscroll.js"></script>
<!-- 滚动条 E -->

<!--commonjs S-->
<script type="text/javascript" src="../../static/js/common.js"></script>
<!--commonjs E-->

</html>
